Course: MySQL Training
Topic: Using START TRANSACTION, COMMIT, and ROLLBACK
Course Link: MySQL Training Course at JustAcademy
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Database transactions are essential for maintaining accurate and consistent data in applications. In MySQL, the three important transaction control statements are START TRANSACTION, COMMIT, and ROLLBACK. These statements allow developers to group related database operations, save successful changes, or cancel uncommitted changes when something goes wrong.
For example, when transferring money between two bank accounts, the database must subtract money from one account and add the same amount to another. If the first operation succeeds but the second fails, the database should not retain only half of the transfer. A transaction allows the application to commit both operations together or roll back the uncommitted changes.
A database transaction is a logical unit of work that contains one or more SQL operations. These operations are treated as a single unit so that the application can decide whether to save the changes or cancel the uncommitted work.
| Statement | Purpose | Example |
|---|---|---|
| START TRANSACTION | Begins a new transaction. | START TRANSACTION; |
| COMMIT | Commits the current transaction and makes its changes permanent. | COMMIT; |
| ROLLBACK | Cancels uncommitted changes made during the current transaction. | ROLLBACK; |
After completing this topic, learners should be able to:
START TRANSACTION.COMMIT.ROLLBACK.The START TRANSACTION statement begins a transaction explicitly. It allows you to execute several related SQL statements and then decide whether to commit or roll back their changes.
START TRANSACTION;
-- Execute one or more SQL statements
COMMIT;
-- Or use ROLLBACK instead of COMMIT if the work must be cancelled.START TRANSACTION;
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 101;
COMMIT;This example increases the salary of employee 101 by 2000 and commits the update. The example assumes that the employees table and employee_id and salary columns exist.
The COMMIT statement ends the current transaction and makes its changes permanent according to the transactional storage engine's guarantees. After a transaction has been committed, a normal ROLLBACK cannot undo those changes.
COMMIT;START TRANSACTION;
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (101, 'Amit Sharma', 35000);
COMMIT;In this example, the employee record is inserted and then committed. The example assumes that the table has the listed columns and that employee ID 101 is available.
Use COMMIT when all required statements have succeeded and the application has verified that the intended business operation is valid.
For example, an online order should not be committed as successfully processed if a required inventory update failed. The application should validate each important step before committing.
The ROLLBACK statement cancels uncommitted changes made during the current transaction. It is useful when a statement fails, an application validation check does not pass, or the operation should not be saved.
ROLLBACK;START TRANSACTION;
UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 101;
ROLLBACK;The salary update is rolled back if the table uses a transactional storage engine such as InnoDB and the transaction has not already been committed.
| Feature | START TRANSACTION | COMMIT | ROLLBACK |
|---|---|---|---|
| Main purpose | Begins a transaction. | Saves the current transaction's changes. | Cancels uncommitted changes. |
| When used | Before executing related operations. | After successful execution and validation. | When the transaction must be cancelled. |
| Changes data? | Not by itself. | Finalizes changes made during the transaction. | Undoes uncommitted changes to transactional tables. |
| Ends the current transaction? | Starts a transaction and can implicitly commit a previously active transaction. | Yes. | Yes. |
| Typical example | Begin an order-processing operation. | Save a successfully processed order. | Cancel an order operation that failed before commit. |
Before working with transaction statements, ensure that you have access to a MySQL database, permission to modify the required tables, and a transactional storage engine.
CREATE DATABASE transaction_demo;
USE transaction_demo;CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
salary DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;InnoDB supports transactions, allowing changes to be committed or rolled back. Other storage engines may not provide the same transaction guarantees.
INSERT INTO employees (employee_id, employee_name, salary)
VALUES
(101, 'Amit Sharma', 35000.00),
(102, 'Neha Verma', 42000.00),
(103, 'Rahul Mehta', 39000.00);SELECT *
FROM employees
ORDER BY employee_id;Use this query to inspect the initial records before experimenting with transactions.
In this example, a salary update is made and then committed. After the commit, the new salary becomes the saved database value.
START TRANSACTION;
UPDATE employees
SET salary = salary + 3000
WHERE employee_id = 101;
SELECT *
FROM employees
WHERE employee_id = 101;
COMMIT;START TRANSACTION begins the transaction.UPDATE increases the salary by 3000.SELECT displays the updated row in the current session.COMMIT saves the change.The SELECT statement before COMMIT lets you inspect the current session's changes. It does not prove that the transaction has been committed. The commit occurs only when the COMMIT statement executes successfully.
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 101;The updated salary should remain stored after the transaction is committed.
In this example, a salary is temporarily increased, but the transaction is rolled back. The uncommitted salary change is cancelled.
START TRANSACTION;
UPDATE employees
SET salary = salary + 10000
WHERE employee_id = 102;
SELECT *
FROM employees
WHERE employee_id = 102;
ROLLBACK;SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 102;The salary should return to the value it had before the transaction began, provided that no other committed operation changed it.
Transactions can control whether new records are saved or cancelled. This is useful when an application needs to insert multiple related records.
START TRANSACTION;
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (104, 'Priya Singh', 45000.00);
COMMIT;The new employee record is committed if the INSERT succeeds.
START TRANSACTION;
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (105, 'Karan Patel', 38000.00);
ROLLBACK;The inserted row is not retained because the transaction is rolled back.
SELECT *
FROM employees
WHERE employee_id IN (104, 105);If the first example was committed and the second was rolled back, employee 104 should be present and employee 105 should not have been inserted by the rolled-back transaction.
DELETE statements can remove rows, so using a transaction provides an opportunity to verify the result before committing the deletion.
START TRANSACTION;
DELETE FROM employees
WHERE employee_id = 103;
COMMIT;The employee row is deleted and the deletion is committed if the statement succeeds.
START TRANSACTION;
DELETE FROM employees
WHERE employee_id = 102;
ROLLBACK;The deletion is cancelled, so the row remains present if it existed before the transaction and the table supports transactions.
Before deleting data, inspect the matching records with SELECT:
SELECT *
FROM employees
WHERE employee_id = 102;Always verify the WHERE condition carefully. A missing or incorrect WHERE clause can affect far more records than intended.
A bank transfer is one of the most common examples of transaction processing. It involves deducting money from one account and adding it to another account.
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_holder VARCHAR(100) NOT NULL,
balance DECIMAL(12,2) NOT NULL
) ENGINE = InnoDB;INSERT INTO accounts (account_id, account_holder, balance)
VALUES
(1, 'Amit Sharma', 10000.00),
(2, 'Neha Verma', 5000.00);START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000.00
WHERE account_id = 1
AND balance >= 1000.00;
UPDATE accounts
SET balance = balance + 1000.00
WHERE account_id = 2;
COMMIT;This example demonstrates the transaction structure, but a production-ready transfer must validate both updates. The application must confirm that the debit updated exactly one row and that the receiver account exists and was updated. If either condition fails, it should issue ROLLBACK instead of COMMIT.
SELECT account_id, account_holder, balance
FROM accounts
ORDER BY account_id;When a transfer succeeds, the sender's balance decreases by 1000 and the receiver's balance increases by 1000. The combined balance remains the same, assuming there are no fees or other transactions.
MySQL normally uses autocommit mode, which is enabled by default. When autocommit is ON, a successful standalone statement outside an explicitly managed transaction is normally committed automatically.
SELECT @@autocommit;A result of 1 means autocommit is enabled. A result of 0 means it is disabled for the current session.
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;If this statement executes outside an explicit transaction while autocommit is enabled, its successful change is normally committed automatically. A later ROLLBACK cannot undo that already committed update.
START TRANSACTION;
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;
ROLLBACK;START TRANSACTION temporarily establishes an explicit transaction boundary. The update is rolled back, assuming the table is transactional and no implicit commit intervenes.
SET autocommit = 0;When autocommit is disabled, changes to transactional tables require explicit transaction completion. The session should issue COMMIT or ROLLBACK as appropriate.
SET autocommit = 1;Changing autocommit from OFF to ON commits an active transaction in MySQL. Therefore, do not execute this statement if your intention is to roll back pending changes.
MySQL supports savepoints, which mark a position within an active transaction. They allow a transaction to undo changes made after a specific point without necessarily rolling back all earlier changes.
START TRANSACTION;
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;
SAVEPOINT salary_updated;
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 102;
ROLLBACK TO SAVEPOINT salary_updated;
COMMIT;The second salary update is rolled back to the savepoint, but the first update remains part of the transaction and is committed. This demonstrates partial rollback and should not be used when both updates must succeed or fail together.
| Command | Purpose |
|---|---|
SAVEPOINT name |
Creates a savepoint inside the active transaction. |
ROLLBACK TO SAVEPOINT name |
Reverts changes made after the named savepoint. |
RELEASE SAVEPOINT name |
Removes the savepoint without committing the transaction. |
| Problem | Possible Cause | Solution |
|---|---|---|
| ROLLBACK does not undo an update | The update was already committed or the table is nontransactional. | Check transaction boundaries and the table's storage engine. |
| Only one part of an operation is saved | The statements were not grouped into one transaction or an implicit commit occurred. | Review the transaction boundaries and SQL statement behavior. |
| COMMIT does not produce the expected result | A statement failed, no rows matched, or the business logic was incorrect. | Inspect errors and affected-row counts before committing. |
| Transaction stays open | The application did not issue COMMIT or ROLLBACK. | Complete the transaction and improve error-handling logic. |
| Changes disappear after an error | The failing statement or transaction was rolled back. | Inspect the error and determine whether the statement or full transaction was rolled back. |
| Deadlock occurs | Concurrent transactions are waiting for conflicting locks. | Keep transactions short, use consistent lock ordering, and handle safe retries. |
| Unexpected rows are updated | The WHERE condition matched more records than intended. | Test the condition with SELECT and verify affected-row counts. |
| Rollback is incomplete across tables | One or more tables use a nontransactional storage engine. | Use transactional tables, typically InnoDB, for related operations. |
Some MySQL statements cause an implicit commit. This means a transaction can be committed automatically even when the application has not explicitly issued COMMIT. Many DDL statements, such as common uses of CREATE TABLE, ALTER TABLE, and DROP TABLE, cause implicit commits.
START TRANSACTION;
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;
CREATE TABLE transaction_test (
test_id INT PRIMARY KEY
);
ROLLBACK;The CREATE TABLE statement can cause an implicit commit, so the earlier salary update may already have been committed. The later ROLLBACK cannot undo a change that was committed before it executed.
Avoid mixing DDL statements with business transactions that depend on complete rollback. Review MySQL's documentation for statements that cause implicit commits before using them inside transaction workflows.
Long-running transactions can hold locks and reduce concurrency. Perform only the required database operations inside a transaction.
Verify that all required statements succeeded and affected the intended rows. Do not commit merely because the SQL commands were submitted.
If an error or failed validation means the business operation is incomplete, roll back the transaction rather than saving partial work.
InnoDB supports transaction rollback, foreign keys, and concurrency control. Ensure that all tables involved in the operation provide the required transactional behavior.
Applications should catch SQL errors, issue ROLLBACK when appropriate, and close or return connections to the connection pool in a safe state. The application should not assume every MySQL error automatically rolls back the whole transaction.
Do not assume a database rollback can reverse an external payment, send a message, or undo an API request. External actions need their own error-handling, retry, and compensation strategies.
Test what happens when every operation succeeds and when an intermediate operation fails. Verify the final database state after both COMMIT and ROLLBACK.
students with student ID, name, and marks columns.SELECT @@autocommit;.Project Objective: Use transaction statements to update employee salaries safely and commit or roll back changes based on validation.
START TRANSACTION;
UPDATE employees
SET salary = salary + 2500
WHERE employee_id = 101;
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 101;
COMMIT;START TRANSACTION;
UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 102;
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 102;
ROLLBACK;The learner should understand that COMMIT saves a successful transaction, whereas ROLLBACK cancels uncommitted changes. The learner should also recognize that the correct transaction outcome depends on storage engine support, statement behavior, and application validation.
It begins an explicit transaction so that multiple related SQL operations can be committed or rolled back together.
COMMIT ends the current transaction and saves its changes according to the transactional storage engine's guarantees.
ROLLBACK cancels uncommitted changes made during the current transaction for transactional tables.
COMMIT saves the transaction's changes, while ROLLBACK cancels its uncommitted changes.
No. A normal ROLLBACK cannot undo a transaction that has already been committed.
InnoDB is the commonly used transactional storage engine in MySQL.
Autocommit is a session setting that normally commits each successful standalone statement automatically when enabled.
They help ensure that related account updates are handled together, reducing the risk of incomplete transfers.
No. Some statements cause implicit commits, and operations on nontransactional tables do not have the same rollback behavior as operations on InnoDB tables.
A savepoint marks a point within a transaction so that changes made after it can be rolled back without necessarily cancelling all earlier work.
The effect depends on the error. The failing statement may be rolled back while the transaction remains active, or the entire transaction may be rolled back. Application code should handle errors explicitly.
Autocommit normally commits each standalone statement automatically. An explicit transaction groups related statements so they can be committed or rolled back as a unit.
START TRANSACTION begins an explicit transaction.COMMIT saves the transaction's changes.ROLLBACK cancels uncommitted changes.START TRANSACTION, COMMIT, and ROLLBACK are fundamental MySQL statements for controlling database changes. START TRANSACTION begins a transaction, COMMIT saves its changes, and ROLLBACK cancels uncommitted work. Together, they help developers build reliable database operations for applications such as banking, employee management, inventory control, and e-commerce.
For correct transaction handling, use transactional tables such as InnoDB, validate every important operation, understand autocommit and implicit commits, and handle errors carefully. Practicing both successful commits and deliberate rollbacks is an effective way to understand transaction behavior.
Explore the MySQL course: MySQL Training Course at JustAcademy
Register for a course demo: Register for a JustAcademy Course Demo
Visit JustAcademy: https://www.justacademy.co/
Official MySQL Documentation: START TRANSACTION, COMMIT, and ROLLBACK Statements | MySQL Transactional and Locking Statements
